<?php

namespace app\modules\cruise\controllers;

use Yii;
use app\modules\cruise\models\Customer;
use app\modules\cruise\models\CustomerSearch;
use app\modules\cruise\models\RoomUse;
use app\modules\cruise\models\Card;
use app\modules\cruise\models\Cruise;
use app\modules\cruise\models\Excel;
use yii\web\Controller;
use yii\web\NotFoundHttpException;
use yii\filters\VerbFilter;
use Overtrue\Pinyin\Pinyin;
use yii\filters\AccessControl;
use yii\data\Pagination;
use yii\data\ArrayDataProvider;

/**
 * CustomerController implements the CRUD actions for Customer model.
 */
class CustomerController extends Controller
{
    /**
     * @inheritdoc
     */
    public function behaviors()
    {
        return [
            'verbs' => [
                'class' => VerbFilter::className(),
                'actions' => [
                    'delete' => ['POST'],
                ],
            ],

             'access' => [
                'class' => AccessControl::className(),
                'rules' => [
                    [
                        'allow' => true,
                        //'actions' => ['list', 'index','create','view','update','delete'],
                        'roles' => ['@'],
                    ],
                ],
            ],
        ];
    }

    /**
     * Lists all Customer models.
     * @return mixed
     */
    public function actionIndex()
    {
        $searchModel = new CustomerSearch();
        $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

        return $this->render('index', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
        ]);
    }


    public function actionListDelete(){
        $searchModel = new CustomerSearch();
        $searchModel->status = 2;
        $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

        return $this->render('index', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
        ]);

    }

    public function actionCancelList(){
        $searchModel = new CustomerSearch();
        $searchModel->status = 1;
        $dataProvider = $searchModel->search_list(Yii::$app->request->queryParams);

        return $this->render('cancel', [
            'searchModel' => $searchModel,
            'dataProvider' => $dataProvider,
        ]);

    }

    public function actionLook($id)
    {
        $model = $this->findModel($id);

        if ($model->load(Yii::$app->request->post()) && $model->save()) {
            return $this->redirect(['cancel-list']);
        }

        return $this->render('look', [
            'model' => $model,
        ]);
    }

    //修改房间
    public function actionChange($id){

        $this->layout = "//empty";

        $model = $this->findModel($id);

        $param = Yii::$app->request->get(); 

        $name = isset($param['username']) ? $param['username'] : '';
        $phone = isset($param['phone']) ? $param['phone'] : '';

        $remark = isset($param['remark']) ? $param['remark'] : '';

        $ord = isset($param['ord']) ? $param['ord'] : '';

        $query = RoomUse::find()->leftJoin('customer', 'customer.room_use_id = room_use.id')->where(['room_use.status'=>1])->andWhere(['not in','room_use.id',$model->room_use_id]);

        if(isset($param['username']) && $param['username']){
            $query->andWhere(['like','customer.name',$param['username']]);
        }
        if(isset($param['phone']) && $param['phone']){
            $query->andWhere(['like','customer.phone',$param['phone']]);
        }

        if(isset($param['remark']) && $param['remark']){
            $query->andWhere(['like','room_use.remark',$param['remark']]);
        }

        if(isset($param['ord']) && $param['ord']){
            $query->andWhere(['room_use.ord'=>$param['ord']]);
        }
    
        $room_use = $query->all();
       
        $provider = new ArrayDataProvider([
              'allModels' => $room_use,
              'pagination' => [
                  'pageSize' => 20,
                  'defaultPageSize'=>20,
              ],
        ]);
        $room_use = $provider->getModels();
        $pages = $provider->pagination;

        return $this->render('change',['model'=>$model,'room_use' => $room_use,
             'pages' => $pages,'phone'=>$phone,'name'=>$name,'remark'=>$remark,'ord'=>$ord]);
    }

    //重新分配房间
    public function actionChangeSave(){

        $cust_id = Yii::$app->request->get("cust_id");
        $room_id  = Yii::$app->request->get("room_id");
      
        $model = Customer::findOne($cust_id);
        
        $room_use = RoomUse::findOne($room_id);         
 
        $model->room_use_id = $room_id;
        $model->room_id = $room_use->room_id;

        if($model->save()){
            echo true;
        }else{
            echo false;
        }
    }

    /**
     * Displays a single Customer model.
     * @param integer $id
     * @return mixed
     * @throws NotFoundHttpException if the model cannot be found
     */
    public function actionView($id)
    {   
        return $this->render('view', [
            'model' => $this->findModel($id),
        ]);
    }

    /**
     * Creates a new Customer model.
     * If creation is successful, the browser will be redirected to the 'view' page.
     * @return mixed
     */
    public function actionCreate($room_use_id)
    {
        $model = new Customer();
        $room_use = RoomUse::findOne($room_use_id);
        if ($model->load(Yii::$app->request->post()) && $model->save()) {
            
            return $this->redirect(['view', 'id' => $model->id]);

        }else{

           $model->room_use_id = $room_use_id; 
           $model->room_id  = $room_use->room_id;
           $model->status = 1;
           $model->passport_type = 1;
           $model->gender = 1;
           $model->nation = '汉';
           $model->country = '中国';
           return $this->render('create', [
                'model' => $model,
                'room_use_id' =>$room_use_id,
           ]);  
        }
    }


    // 获取文字拼音
    public function actionPinyin(){//{{{

        $name = Yii::$app->request->get("name");

        $pinyin = new Pinyin();

        $ret = $pinyin->sentence($name);

        print_r(str_replace(' ', '', strtoupper($ret)));
    }//}}}

    // 生成护照截止日期
    public function actionPassport(){//{{{
        
        $passport = Yii::$app->request->get("passport");
        $date = strtotime($passport);

        $year = strtotime("+10 year", $date);   // 十年
        $day = strtotime("-1 day", $year);      // 一天前
        
        print_r(date("Y-m-d", $day));
    }//}}}

    // 根据身份证获取用户信息 [ 签发地, 性别, 出生日期, 出生地 ]
    public function actionCard(){//{{{
        \Yii::$app->response->format = \yii\web\Response::FORMAT_JSON;  

        $card = Yii::$app->request->get("card");
        // 1. 截取前六位  判断地区

        if($card){
            $area = substr($card, 0, 6); 
            $area2 = substr($card, 0, 3);
            
            $model = Card::find()->where(['card'=>$area])->one();
            $model2 = Card::find()->where([ 'card'=>sprintf("%d000",$area2) ])->one();

            if($model){
                $message['card_address'] = $model->area;
            }
            if($model2){
                $message['native'] = $model2->area;
            }

            $birthday = substr($card, 6, 8);
            $message['birthday'] = date("Y-m-d", strtotime($birthday));

            $gender = substr($card, 16, 1);
            if($gender%2 == 0){
                
                $message['gender'] = 2; // substr($card, 16, 1);
            }else{
            
                $message['gender'] = 1;
            }
        }
        //print_r(json_encode($message));
        return $message;
        
    }//}}}


    //  获取指定用户信息
    public function actionGetMessage($id){//{{{
        $this->layout = false;

        $model = $this->findModel($id);

        return $this->render('detail', [
            'model' => $model,
        ]);


        if($model){
            print_r($model);
        }else{
            echo "";
        }
    }//}}}


    //取消
    public function actionStatus(){
        $ids = \yii::$app->request->post('people_id');
        if($ids){
            foreach($ids as $id){
                $model = Customer::findOne($id);
                $model->cancel = 3;
                $model->save();
            }
            echo 1;
            exit();
        }
        echo 0;

    }

    /**
     * Updates an existing Customer model.
     * If update is successful, the browser will be redirected to the 'view' page.
     * @param integer $id
     * @return mixed
     * @throws NotFoundHttpException if the model cannot be found
     */
    public function actionUpdate($id)
    {
        $model = $this->findModel($id);

        if ($model->load(Yii::$app->request->post()) && $model->save()) {
            return $this->redirect(['view', 'id' => $model->id]);
        }

        return $this->render('update', [
            'model' => $model,
        ]);
    }

    /**
     * Deletes an existing Customer model.
     * If deletion is successful, the browser will be redirected to the 'index' page.
     * @param integer $id
     * @return mixed
     * @throws NotFoundHttpException if the model cannot be found
     */
    public function actionDelete($id)
    {
        $this->findModel($id)->delete();

        return $this->redirect(['index']);
    }

    /**
     * Finds the Customer model based on its primary key value.
     * If the model is not found, a 404 HTTP exception will be thrown.
     * @param integer $id
     * @return Customer the loaded model
     * @throws NotFoundHttpException if the model cannot be found
     */
    protected function findModel($id)
    {
        if (($model = Customer::findOne($id)) !== null) {
            return $model;
        }

        throw new NotFoundHttpException('The requested page does not exist.');
    }


      //生成名单
    public function actionCreateExcel($cruise_id,$template_id)
    {   
       
        $cruise = Cruise::findOne($cruise_id);
        $order_num = $people_num = 0;
        $query = RoomUse::find()->where(['status'=>1]); 
        $room_num = $query->count();
        $room_use = $query->all();

        foreach($room_use as $r){
            $people_num += $r->customerNums;
        }

        if($template_id  == 7){

            //获取团队
            $assign = Customer::find()->groupby('group')->all();

            $group_arr = [];
            foreach($assign as $as){
                $group_arr[$as->group] = $as->group;
            }
            sort($group_arr);
           
            return $this->render("template_1",['cruise'=>$cruise,'template_id'=>$template_id,'group_arr'=>$group_arr]);
        }else{

            return $this->render("template",['cruise'=>$cruise,'template_id'=>$template_id,'room_num'=>$room_num,'people_num'=>$people_num]); 
        }
       

    }

    

    //创建Excel
    public function actionExcel(){
        
        $data = yii::$app->request->post();

        $excel = new Excel();
        $res = ['status' => 0 ,'msg'=> '' , 'data'=>''];
        if(empty($data)){
            print_r(json_encode($res));
        }
        $files = ['status'=>1,'msg'=>'','data'=>''];

        $zip = new \ZipArchive;//打包
        $filename = sprintf("%s.zip",uniqid()); 
        $file = $filename;
        $filename = str_replace("\"","",$filename); //删除双引号
        $filename = str_replace("'","",$filename);  //删除单引号
        $filename = str_replace("\\","",$filename); //删除斜杠
        $filename = preg_replace("/\s/","",$filename); //去除空格

        //文件名
        $file_url_name = sprintf("世界梦领队名单.zip");  

        $filename = sprintf("%s/web/uploads/zip/%s",Yii::$app->basePath,$filename);
        $filename = iconv("utf-8","gbk//ignore",$filename); //中文乱码
        $res = $zip->open($filename, \ZipArchive::OVERWRITE);//如果打包文件有就覆盖

        
        $template_id = isset($data['template_id']) ? $data['template_id'] : '';
        $cruise_id = isset($data['cruise_id']) ? $data['cruise_id'] : '';

        $assign_arr = isset($data['assign_arr']) ? $data['assign_arr'] : '';

        if($data['template_id'] == 3){
            $files['data'] = $excel->Template3($cruise_id);
            print_r(json_encode($files));
            exit();
        }else if($data['template_id'] == 4){
            $files['data'] = $excel->Template4($cruise_id);
            print_r(json_encode($files));
            exit();
        }else if($data['template_id'] == 5){
            $files['data'] = $excel->Template5($cruise_id);
            print_r(json_encode($files));
            exit();
        }else if($data['template_id'] == 6){
            $files['data'] = $excel->Template6($cruise_id);
            print_r(json_encode($files));
            exit();
        }else if($data['template_id'] == 7){

            if($assign_arr){
               
                if($res === TRUE) {
                    foreach($assign_arr as $v){

                        $res = $excel->Template7($cruise_id,$v['lead_id']);
                        
                        $down_url = iconv("utf-8","gbk//ignore",$res['down_url']); //中文乱码
                        $down_name = iconv("utf-8","gbk//ignore",$res['down_name']); //中文乱码
                        $zip->addFile($down_url,sprintf("%s.xlsx",$down_name));  
                    }
                    $zip->close();
                }   
            }
            $files['data'] = ['down_name'=>$file_url_name,'down_url'=>'uploads/zip/'.$file];
            print_r(json_encode($files));
            exit();

        }else if($data['template_id'] == 8){
            $files['data'] = $excel->Template8($cruise_id);
            print_r(json_encode($files));
            exit();
        }else if($data['template_id'] == 9){
            $files['data'] = $excel->Template9($cruise_id);
            print_r(json_encode($files));
            exit();
        }
        print_r(json_encode($res));
    }

    //下载Excel文件
    public function actionDownloadFile($name,$url){
        $url = iconv("utf-8","gb2312",$url); 
        if(file_exists($url)){
            header("Content-type: application/octet-stream");    
            header("Accept-Ranges: bytes");    
            header("Accept-Length: " . filesize($url));    
            header("Content-Disposition: attachment; filename=" .$name);    
            @readfile($url);
        }else{
            return false;
        }       

    }        

    //签证办理导出
    public function actionVisaExcel(){

        $objPHPExcel = new \PHPExcel();

        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('签证办理');
        $sheet->getDefaultStyle()->getFont()->setSize(11)->setName('宋体');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        
        $sheet->getColumnDimension('A')->setWidth(8.5);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(11);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(13);// 设置宽度
        $sheet->getColumnDimension('D')->setWidth(14);// 设置宽度
        $sheet->getColumnDimension('E')->setWidth(21);// 设置宽度
        $sheet->getColumnDimension('F')->setWidth(7);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(14);// 设置宽度
        $sheet->getColumnDimension('H')->setWidth(15.5);// 设置宽度
        $sheet->getColumnDimension('I')->setWidth(12.5);// 设置宽度
        $sheet->getColumnDimension('J')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('K')->setWidth(11);// 设置宽度

        $array_title_1 = ['序号','房间号','名（拼音）','姓（拼音）','中文姓名','性别',  '出生日期','护照号','护照签发日','护照到期日','国籍'];

        $sheet->fromArray($array_title_1, NULL, 'A1');
        $sheet->getStyle('A1:K1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFE1FF');
        $sheet->getStyle('A1:K1')->getFont()->setBold(true)->getColor()->setARGB('000000');
        $sheet->getRowDimension(1)->setRowHeight(24);

        $country_code_2 = yii::$app->params['area']['country_code_2'];

        $customers = Customer::find()->where(['status'=>1,'cancel'=>1])->orderBy('group')->all();  

        $key = 0;
        foreach($customers as $val){
            
             $sheet->setCellValue(sprintf("A%d",$key+2),$key+1)
                ->setCellValue(sprintf("B%d",$key+2),$val->roomUse->code)
                ->setCellValue(sprintf("C%d",$key+2),$val->en_first_name)
                ->setCellValue(sprintf("D%d",$key+2),$val->en_last_name)
                ->setCellValue(sprintf("E%d",$key+2),$val->first_name.$val->last_name)
                ->setCellValue(sprintf("F%d",$key+2),yii::$app->params['gender_en'][$val->gender]) 
                ->setCellValue(sprintf("G%d",$key+2),date('Y/m/d',strtotime($val->birthday)))
                ->setCellValue(sprintf("H%d",$key+2),$val->passport)
                ->setCellValue(sprintf("I%d",$key+2),date('Y/m/d',strtotime($val->passport_grant)))
                ->setCellValue(sprintf("J%d",$key+2),date('Y/m/d',strtotime($val->passport_date)))
                ->setCellValue(sprintf("K%d",$key+2),(!empty($country_code_2[$val->country]))?$country_code_2[$val->country]:$val->country);

                $key++;
        }       
  
        $styleArray = [
             'borders' => [
                 'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
             ],
        ]; //设置border
        $sheet->getStyle(sprintf('A1:K%s',$key+1))->applyFromArray($styleArray);


        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        
        $file_name = sprintf("%s.xlsx",'签证办理表');

        $file = iconv("utf-8","gb2312",$file); 
        if(file_exists($file)){
            header("Content-type: application/octet-stream");    
            header("Accept-Ranges: bytes");    
            header("Accept-Length: " . filesize($file));    
            header("Content-Disposition: attachment; filename=" .$file_name);    
            @readfile($file);
        }else{
            return false;
        }       
        
    }

    //取消人员导出
    public function actionCancelExcel(){

        $objPHPExcel = new \PHPExcel();

        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('取消人员');
        $sheet->getDefaultStyle()->getFont()->setSize(11)->setName('宋体');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        
        $sheet->getColumnDimension('A')->setWidth(8.5);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(11);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(13);// 设置宽度
        $sheet->getColumnDimension('D')->setWidth(14);// 设置宽度
        $sheet->getColumnDimension('E')->setWidth(14);// 设置宽度
        $sheet->getColumnDimension('F')->setWidth(14);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(14);// 设置宽度
        $sheet->getColumnDimension('H')->setWidth(11);// 设置宽度
        $sheet->getColumnDimension('I')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('J')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('K')->setWidth(11);// 设置宽度
        $sheet->getColumnDimension('L')->setWidth(11);// 设置宽度

        $array_title_1 = ['序号','取消时间','体系','团号（升序）','中文姓名','姓（拼音）','名（拼音）','性别','护照号','出生日期','备注','备注'];

        $sheet->fromArray($array_title_1, NULL, 'A1');
        $sheet->getStyle('A1:L1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFE1FF');
        $sheet->getStyle('A1:L1')->getFont()->setBold(true)->getColor()->setARGB('000000');
        $sheet->getRowDimension(1)->setRowHeight(24);

        
        $customers = Customer::find()->where(['status'=>1,'cancel'=>3])->asArray()->all();
        $customers = $this->arraySequence($customers,'group','SORT_ASC');
 
        $key = 0;
        foreach($customers as $val){
            
             $sheet->setCellValue(sprintf("A%d",$key+2),$key+1)
                ->setCellValue(sprintf("B%d",$key+2),date('Y/m/d',strtotime($val['updated_at'])))
                ->setCellValue(sprintf("C%d",$key+2),$val['supply'])
                ->setCellValue(sprintf("D%d",$key+2),$val['group'])
                ->setCellValue(sprintf("E%d",$key+2),$val['first_name'].$val['last_name'])
                ->setCellValue(sprintf("F%d",$key+2),$val['en_first_name'])
                ->setCellValue(sprintf("G%d",$key+2),$val['en_last_name'])
                
                ->setCellValue(sprintf("H%d",$key+2),yii::$app->params['gender'][$val['gender']]) 
                ->setCellValue(sprintf("I%d",$key+2),$val['passport'])
                ->setCellValue(sprintf("J%d",$key+2),date('Y/m/d',strtotime($val['birthday'])));
                $key++;
        }       
  
        $styleArray = [
             'borders' => [
                 'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
             ],
        ]; //设置border
        $sheet->getStyle(sprintf('A1:L%s',$key+1))->applyFromArray($styleArray);


        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        
        $file_name = sprintf("%s.xlsx",'取消人员表');

        $file = iconv("utf-8","gb2312",$file); 
        if(file_exists($file)){
            header("Content-type: application/octet-stream");    
            header("Accept-Ranges: bytes");    
            header("Accept-Length: " . filesize($file));    
            header("Content-Disposition: attachment; filename=" .$file_name);    
            @readfile($file);
        }else{
            return false;
        }       
        
    }


     //未签到人员导出
    public function actionUnsignExcel(){

        $objPHPExcel = new \PHPExcel();

        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('未签到人员');
        $sheet->getDefaultStyle()->getFont()->setSize(11)->setName('宋体');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        
        $sheet->getColumnDimension('A')->setWidth(8.5);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(11);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(13);// 设置宽度
        $sheet->getColumnDimension('D')->setWidth(14);// 设置宽度
        $sheet->getColumnDimension('E')->setWidth(14);// 设置宽度
        $sheet->getColumnDimension('F')->setWidth(14);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(14);// 设置宽度
        $sheet->getColumnDimension('H')->setWidth(11);// 设置宽度
        $sheet->getColumnDimension('I')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('J')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('K')->setWidth(11);// 设置宽度
        $sheet->getColumnDimension('L')->setWidth(11);// 设置宽度

        $array_title_1 = ['序号','更新时间','体系','团号（升序）','中文姓名','姓（拼音）','名（拼音）','性别','护照号','出生日期','备注','备注'];

        $sheet->fromArray($array_title_1, NULL, 'A1');
        $sheet->getStyle('A1:L1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFE1FF');
        $sheet->getStyle('A1:L1')->getFont()->setBold(true)->getColor()->setARGB('000000');
        $sheet->getRowDimension(1)->setRowHeight(24);

        
        $customers = Customer::find()->where(['status'=>1,'sign'=>0])->andWhere(['<','cancel',3])->asArray()->all();
        $customers = $this->arraySequence($customers,'group','SORT_ASC');
 
        $key = 0;
        foreach($customers as $val){
            
             $sheet->setCellValue(sprintf("A%d",$key+2),$key+1)
                ->setCellValue(sprintf("B%d",$key+2),date('Y/m/d',strtotime($val['updated_at'])))
                ->setCellValue(sprintf("C%d",$key+2),$val['supply'])
                ->setCellValue(sprintf("D%d",$key+2),$val['group'])
                ->setCellValue(sprintf("E%d",$key+2),$val['first_name'].$val['last_name'])
                ->setCellValue(sprintf("F%d",$key+2),$val['en_first_name'])
                ->setCellValue(sprintf("G%d",$key+2),$val['en_last_name'])
                
                ->setCellValue(sprintf("H%d",$key+2),yii::$app->params['gender'][$val['gender']]) 
                ->setCellValue(sprintf("I%d",$key+2),$val['passport'])
                ->setCellValue(sprintf("J%d",$key+2),date('Y/m/d',strtotime($val['birthday'])));
                $key++;
        }       
  
        $styleArray = [
             'borders' => [
                 'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
             ],
        ]; //设置border
        $sheet->getStyle(sprintf('A1:L%s',$key+1))->applyFromArray($styleArray);


        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        
        $file_name = sprintf("%s.xlsx",'未签到人员表');

        $file = iconv("utf-8","gb2312",$file); 
        if(file_exists($file)){
            header("Content-type: application/octet-stream");    
            header("Accept-Ranges: bytes");    
            header("Accept-Length: " . filesize($file));    
            header("Content-Disposition: attachment; filename=" .$file_name);    
            @readfile($file);
        }else{
            return false;
        }       
        
    }
    private function arraySequence($array, $field, $sort = 'SORT_DESC')
    {
        $arrSort = array();
        foreach ($array as $uniqid => $row) {
            foreach ($row as $key => $value) {
                $arrSort[$key][$uniqid] = $value;
            }
        }
        array_multisort($arrSort[$field], constant($sort), $array);
        return $array;
    }
}
